with change_today as (
    select
        id,
        network_id,
        delivery_shape,
        delivery_type,
        g_id,
        g_name,
        g_center,
        g_points,
        g_radius,
        g_valid_time,
        g_create_time,
        expiry_date,
        create_by,
        update_by,
        create_by_name,
        update_by_name,
        create_time,
        update_time,
        network_type_id
    from jms_dwd.dwd_yl_cn_mysql_lmdm_sys_network_electronic_fence_base_dt
    where dt='{{ execution_date | cst_ds }}' and delivery_type=2
),
     change_lastday as (
         select
             id,
             network_id,
             delivery_shape,
             delivery_type,
             g_id,
             g_name,
             g_center,
             g_points,
             g_radius,
             g_valid_time,
             g_create_time,
             expiry_date,
             create_by,
             update_by,
             create_by_name,
             update_by_name,
             create_time,
             update_time,
             network_type_id
         from jms_dwd.dwd_yl_cn_mysql_lmdm_sys_network_electronic_fence_base_dt
         where dt='{{ execution_date | date_add(-1) | cst_ds }}' and delivery_type=2
     )
insert overwrite table ai_dwd.cn_mysql_lmdm_sys_network_electronic_fence_change PARTITION (dt='{{ execution_date | cst_ds_nodash }}')
select
    e.id,
    e.network_id,
    f.code as network_code,
    f.name as network_name,
    e.delivery_shape,
    e.delivery_type,
    e.g_id,
    e.g_name,
    e.g_center,
    e.g_points,
    e.g_radius,
    e.g_valid_time,
    e.g_create_time,
    e.expiry_date,
    e.create_by,
    e.update_by,
    e.create_by_name,
    e.update_by_name,
    e.create_time,
    e.update_time,
    e.network_type_id,
    e.change_time,
    e.change_type
from(
        select
            a.id,
            a.network_id,
            a.delivery_shape,
            a.delivery_type,
            a.g_id,
            a.g_name,
            a.g_center,
            a.g_points,
            a.g_radius,
            a.g_valid_time,
            a.g_create_time,
            a.expiry_date,
            a.create_by,
            a.update_by,
            a.create_by_name,
            a.update_by_name,
            a.create_time,
            a.update_time,
            a.network_type_id,
            a.update_time as change_time,
            2 as change_type--变更
        from
            change_today a
                join
            change_lastday b
            on a.network_id=b.network_id
        where a.g_points != b.g_points
        union
        select
            d.id,
            d.network_id,
            d.delivery_shape,
            d.delivery_type,
            d.g_id,
            d.g_name,
            d.g_center,
            d.g_points,
            d.g_radius,
            d.g_valid_time,
            d.g_create_time,
            d.expiry_date,
            d.create_by,
            d.update_by,
            d.create_by_name,
            d.update_by_name,
            d.create_time,
            d.update_time,
            d.network_type_id,
            d.update_time as change_time,
            0 as change_type--消失
        from
            change_today c
                right join
            change_lastday d
            on c.network_id=d.network_id
        where c.network_id is null
        union
        select
            g.id,
            g.network_id,
            g.delivery_shape,
            g.delivery_type,
            g.g_id,
            g.g_name,
            g.g_center,
            g.g_points,
            g.g_radius,
            g.g_valid_time,
            g.g_create_time,
            g.expiry_date,
            g.create_by,
            g.update_by,
            g.create_by_name,
            g.update_by_name,
            g.create_time,
            g.update_time,
            g.network_type_id,
            g.update_time as change_time,
            1 as change_type--新增
        from
            change_today g
                left join
            change_lastday f
            on g.network_id=f.network_id
        where f.network_id is null
    ) e
        left join
        (select * from jms_dim.dim_lmdm_sys_network)f
    on e.network_id=f.id
distribute by 1;